package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.google.gson.Gson;

public class Conn_DB {

//	public static void main(String[] args) {
//		Conn_DB connectDB = new Conn_DB();
//		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
//		list = connectDB.returnResult("hyks", "2020-06-01", "2020-06-30");
//		Gson gson = new Gson();
//		String rest_json = gson.toJson(list);
//		System.out.println(rest_json);
//	}

	public List<HashMap<String, String>> returnResult(String spot_name, String load_start_time, String load_end_time) {
		Connection con = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://182.92.239.30:3306/ks?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
		String user = "root";
		String password = "root";
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		// 选取数据库方法
//	System.out.println("进入数据库这里的数据："+"spot_name:"+spot_name+"sel_year:"+sel_year+"sel_month"+sel_month);
//	String sql=sel_db(spot_name, sel_year, sel_month);
		// 测试使用
//	spot_name="hyks";
		System.out.println("进入数据库这里的数据：" + "spot_name:" + spot_name + " load_start_time:" + load_start_time
				+ " load_end_time:" + load_end_time);
		String sql = sel_db_byspot_and_time(spot_name, load_start_time, load_end_time);
		Gson gson = new Gson();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);

		} catch (Exception e) {
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		try {
//		preparedStatement=con.prepareStatement("select * from 5mine_quack_results where type like '%"+dataString+"%'");
			preparedStatement = con.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("x", resultSet.getString(3));
				map.put("y", resultSet.getString(4));
				map.put("quackTime", resultSet.getString(6));
				map.put("quackGrade", resultSet.getString(7));
				map.put("nengliang", resultSet.getString(11));
				map.put("tensor", resultSet.getString(13));
				map.put("b_value", resultSet.getString(14));
				list.add(map);
			}
		} catch (Exception e) {
			System.out.println("连接失败");
			e.printStackTrace();
		}
		// 打印json
		String json = gson.toJson(list);
		System.out.println(json);
//	for(HashMap<String, String>lists:list) {
//		System.out.println(lists);
//	}
		return list;
	}

//按照时间+地点进行筛选数据
	public String sel_db_byspot_and_time(String spot_name, String load_start_time, String load_end_time) {
		// 设置初始值防止程序出现问题造成崩溃
		String db_nameString = "SELECT * FROM mine_quake_results_hongyang\r\n" + "WHERE quackTime>='2020-07-01'\r\n"
				+ "AND quackTime<='2020-07-31'\r\n" + "ORDER BY quackTime;";
		// 红阳三矿入口
		if (spot_name.equals("hyks")) {
			System.out.println("进入红阳三矿");
			db_nameString = "SELECT * FROM mine_quake_results_hongyang\r\n" + "WHERE quackTime>='" + load_start_time + "'\r\n"
					+ "AND quackTime<='" + load_end_time + "'\r\n" + "ORDER BY quackTime";
		}
		// 由于数据暂时缺失的缘故，遂当前仅使用红阳三矿的数据进行测试
		// 平顶山数据源入口
		else if (spot_name.equals("pds")) {
			System.out.println("进入平顶山矿井");
			db_nameString = "SELECT * FROM mine_quake_results_hongyang\r\n" + "WHERE quackTime>='" + load_start_time + "'\r\n"
					+ "AND quackTime<='" + load_end_time + "'\r\n" + "ORDER BY quackTime";
		}
		// 山西大同数据源入口
		else if (spot_name.equals("sxts")) {
			System.out.println("进入山西大同矿井");
			db_nameString = "SELECT * FROM mine_quake_results_hongyang\r\n" + "WHERE quackTime>='" + load_start_time + "'\r\n"
					+ "AND quackTime<='" + load_end_time + "'\r\n" + "ORDER BY quackTime";
		}
		// 双鸭山矿井数据源入口
		else {
			System.out.println("进入双鸭山矿井");
			db_nameString = "SELECT * FROM mine_quake_results_hongyang\r\n" + "WHERE quackTime>='" + load_start_time + "'\r\n"
					+ "AND quackTime<='" + load_end_time + "'\r\n" + "ORDER BY quackTime";
		}
		return db_nameString;
	}

}
